﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using QLDHS.DO;
namespace QLDHS.DAL
{
    class CHUCVU_DAL
    {

        OleDbConnection conn = Program.GetConnection();

        public DataSet loadAllhs()
        {
            DataSet ds = new DataSet();
            try
            {
                conn.Open();
                string sql = "select * from CHUCVU";
                OleDbDataAdapter adp = new OleDbDataAdapter(sql, conn);
                adp.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return ds;

        }

        public int insertCV(CHUCVU_DO cv)
        {
            int kq = 0;
            try
            {
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    string text = "INSERT INTO CHUCVU VALUES (?,?,?)";
                    cmd.CommandText = text;
                    try
                    {
                        conn.Open();
                        cmd.Parameters.AddWithValue("@MACV", cv.NvmaCV);
                        cmd.Parameters.AddWithValue("@TENCV", cv.NvtenCV);
                        cmd.Parameters.AddWithValue("@HESOPHUCAP", cv.Nvhesophucap);
                        kq = cmd.ExecuteNonQuery();
                    }
                    catch (OleDbException ex)
                    {
                        throw ex;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return kq;
        }
        public int updateCV(CHUCVU_DO cv)
        {
            int kq = 0;
            try
            {
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    string text = "update  CHUCVU set MACV=?,TENCV=?,HESOPHUCAP=?";
                    cmd.CommandText = text;
                    try
                    {
                        conn.Open();
                        cmd.Parameters.AddWithValue("@MACV", cv.NvmaCV);
                        cmd.Parameters.AddWithValue("@TENCV", cv.NvtenCV);
                        cmd.Parameters.AddWithValue("@HESOPHUCAP", cv.Nvhesophucap);
                        kq = cmd.ExecuteNonQuery();
                    }
                    catch (OleDbException ex)
                    {
                        throw ex;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return kq;
        }
        public int delDV(string macv)
        {
            int kqdel = 0;
            try
            {
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    string text = "DELETE FROM  CHUCVU WHERE MACV=?";
                    cmd.CommandText = text;
                    try
                    {
                        conn.Open();
                        cmd.Parameters.AddWithValue("@MACV", macv);
                        kqdel = cmd.ExecuteNonQuery();
                    }
                    catch (OleDbException ex)
                    {
                        throw ex;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return kqdel;
        }

        public DataTable SearchByname(string name)
        {
            DataTable allcv = new DataTable();

            try
            {
                conn.Open();

                OleDbCommand cmd = new OleDbCommand("select * fom CHUCVU where TENCHUCVU like  '%" + name+ " %'", conn);
                OleDbDataAdapter adp = new OleDbDataAdapter(cmd);

               adp.Fill(allcv);

            }
            finally
            {
                conn.Close();
            }


            return allcv;

        }
    }
}
